package com.db.sql;


import com.common.utils.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.regex.Matcher;

import static java.util.regex.Pattern.compile;

/**
 * SQL构建工具类
 *
 * @author xin.ding
 * @date 2019/12/20 17:42
 */
public class SqlBuilder {
    private static final Logger logger = LoggerFactory.getLogger(SqlBuilder.class);

    /**
     * 插入操作
     */
    static final String INSERT_OPERATION = "I";
    /**
     * 更新操作
     */
    static final String UPDATE_OPERATION = "U";
    /**
     * 删除操作
     */
    static final String DELETE_OPERATION = "D";
    /**
     * 全量清空表操作
     */
    static final String TRANSLATE_OPERATION = "T";
    /**
     * Merge 操作
     */
    static final String MERGE_OPERATION = "M";

    private SqlBuilder() {
        throw new IllegalAccessError();
    }


    /**
     * 构建Insert预编译语句
     *
     * @param table  插入的表
     * @param fields 插入的字段
     * @return 带占位符SQL语句
     */
    public static String buildInsertSql(String table, String[] fields) {
        final StringBuilder insertFields = new StringBuilder();
        final StringBuilder placeHolder = new StringBuilder();

        for (String field : fields) {
            insertFields.append(field).append(",");
            placeHolder.append("?,");
        }

        return String.format("INSERT INTO %s (%s) VALUES (%s)"
                , table
                , insertFields.substring(0, insertFields.lastIndexOf(","))
                , placeHolder.substring(0, placeHolder.lastIndexOf(",")));
    }

    /**
     * 构建Update语句
     *
     * @param table      要更新的表
     * @param fields     要更新的字段
     * @param conditions 更新条件字段
     * @return 带占位符Update语句
     */
    public static String buildUpdateSql(String table, String[] fields, String[] conditions) {
        final StringBuilder updateFields = new StringBuilder();

        for (String field : fields) {
            updateFields.append(field).append("=?").append(",");
        }

        return String.format("UPDATE %s SET %s WHERE %s "
                , table
                , updateFields
                , buildWhereCondition(conditions));
    }

    /**
     * 构建Delete语句
     *
     * @param table      要删除的表
     * @param conditions 删除条件字段
     * @return 带占位符Delete语句
     */
    public static String buildDeleteSql(String table, String[] conditions) {
        return String.format("DELETE FROM %s WHERE %s "
                , table
                , buildWhereCondition(conditions));
    }

    /**
     * 构建Merge语句
     *
     * @param table      要更新的表
     * @param fields     要更新的字段
     * @param conditions 更新条件字段
     * @return 带占位符Update语句
     */
    public static String buildMergeSql(String table, String[] fields, String[] conditions) {
        final StringBuilder insertPlaceHolder = new StringBuilder();
        boolean isFirst = true;

        for (int i = 0; i < fields.length; i++) {
            if (isFirst) {
                insertPlaceHolder.append("?");
                isFirst = false;
            } else {
                insertPlaceHolder.append(",?");
            }
        }

        final StringBuilder updatePlaceHolder = new StringBuilder();
        for (String field : fields) {
            if (ArrayUtils.containsNoCase(conditions, field)) {
                continue;
            }
            updatePlaceHolder.append(field).append(" = ? ,");
        }
        //USING DUAL -->USING(SELECT DISTINCT * FROM DUAL)
        return String.format("MERGE INTO %s USING DUAL ON (%s) WHEN NOT MATCHED THEN INSERT (%s) VALUES (%s) WHEN MATCHED THEN UPDATE SET %s "
                , table
                , buildWhereCondition(conditions)
                , StringUtils.join(fields, ",")
                , insertPlaceHolder
                , updatePlaceHolder.deleteCharAt(updatePlaceHolder.length() - 1));

    }

    /**
     * 构建Truncate语句
     *
     * @param table 要删除的表
     * @return Truncate语句
     */
    public static String buildTruncateSql(String table) {
        return String.format("TRUNCATE TABLE %s", table);
    }

    /**
     * 构建SQL语句Where条件（支持 字段=？条件）
     *
     * @param conditions 条件字段
     * @return 构建后的SQL语句条件部分
     */
    private static String buildWhereCondition(String[] conditions) {
        final StringBuilder conditionStrBuilder = new StringBuilder();
        boolean isFirst = true;

        for (String condition : conditions) {
            if (isFirst) {
                conditionStrBuilder.append(condition).append("=?");
                isFirst = false;
            } else {
                conditionStrBuilder.append(" and ").append(condition).append("=?");
            }
        }

        return conditionStrBuilder.toString();
    }
    //----------------------------- build Sql end -----------------------------

    //----------------------------- set placeHolder value start -----------------------------

    /**
     * Insert 语句占位符赋值
     *
     * @param ps     PreparedStatement
     * @param fields 字段集
     * @param values 数据集
     * @throws SQLException SQL执行异常
     */
    public static void setInsertValue(PreparedStatement ps, String[] fields, Object[] values) throws SQLException {
        if (fields == null) {
            return;
        }
        if (fields.length != values.length) {

        }

        for (int i = 0; i < fields.length; i++) {
            ps.setObject(i + 1, values[i]);
        }
    }

    /**
     * Update语句占位符赋值
     *
     * @param ps         PreparedStatement
     * @param fields     需赋值字段数组
     * @param conditions 需赋值条件字段数组
     * @param values     数据
     * @throws SQLException SQL执行异常
     */
    public static void setUpdateValue(PreparedStatement ps, String[] fields, String[] conditions, Object[] values) {
        try {
            int updateIndex = 1;

            for (int i = 0; i < fields.length; i++) {
                ps.setObject(updateIndex++, values[i]);
            }

            for (String condition : conditions) {
                int pos = ArrayUtils.indexOfNoCase(fields, condition);
                ps.setObject(updateIndex++, values[pos]);
            }
        } catch (Exception e) {
            throw new SqlBuildException("set update values failed, " + e.getMessage());
        }
    }

    /**
     * Delete语句占位符赋值
     *
     * @param ps         PreparedStatement
     * @param conditions 需赋值条件字段数组
     * @param values     数据
     * @throws SQLException SQL执行异常
     */
    public static void setDeleteValue(PreparedStatement ps, String[] fields, String[] conditions, Object[] values) {
        try {


            int deleteIndex = 1;
            for (String condition : conditions) {
                int pos = ArrayUtils.indexOfNoCase(fields, condition);
                ps.setObject(deleteIndex++, values[pos]);
            }
        } catch (Exception e) {
            throw new SqlBuildException("set delete values failed, " + e.getMessage());
        }
    }

    /**
     * Merge语句占位符赋值
     *
     * @param ps         PreparedStatement
     * @param fields     需赋值字段数组
     * @param conditions 需赋值条件字段数组
     * @param values     数据
     * @throws SQLException SQL执行异常
     */
    public static void setMergeValue(PreparedStatement ps, String[] fields, String[] conditions, Object[] values) {

        try {


            if (fields == null) {
                return;
            }

            int index = 1;
            //设置on条件占位符值
            for (String condition : conditions) {
                int pos = ArrayUtils.indexOfNoCase(fields, condition);
                ps.setObject(index++, values[pos]);
            }
            //设置INSERT占位符值
            for (int i = 0; i < fields.length; i++) {
                ps.setObject(index++, values[i]);
            }
            //设置UPDATE占位符值
            for (int i = 0; i < fields.length; i++) {
                if (ArrayUtils.containsNoCase(conditions, fields[i])) {
                    continue;
                }
                ps.setObject(index++, values[i]);
            }
        } catch (Exception e) {
            throw new SqlBuildException("set merge values failed, " + e.getMessage());
        }
    }

    //----------------------------- set placeHolder value start -----------------------------

    //----------------------------- debug sql start -----------------------------

    /**
     * 预编译后的SQL语句
     *
     * @param sql            带有 ? 的占位符的SQL
     * @param values         数据
     * @param fieldNames     目标表字段
     * @param primaryColumns 目标表主键字段
     */
    public static String getRealSql(String sql, Object[] values, String[] fieldNames, String[] primaryColumns) {
        int columns = values.length - 1;
        String operation = getOperation(sql);

        int paramSize = getParamSize(operation, columns, primaryColumns.length);
        if (!match(sql, paramSize)) {
            logger.error("SQL 语句中的占位符与参数个数不匹配。SQL：【{}】，参数个数：【{}】", sql, paramSize);
            return "";
        }
        Object[] objects = new Object[paramSize];
        int index = 0;
        if (INSERT_OPERATION.equals(operation) || UPDATE_OPERATION.equals(operation)) {
            for (int i = 0; i < columns; i++) {
                Object value = objects[i];
                setValues(objects, index, value);
                index++;
            }
        }
        if (UPDATE_OPERATION.equals(operation) || DELETE_OPERATION.equals(operation)) {
            for (String primaryColumn : primaryColumns) {
                int pos = ArrayUtils.indexOfNoCase(fieldNames, primaryColumn);
                Object value = objects[pos];
                setValues(objects, index, value);
                index++;
            }
        }
        if (MERGE_OPERATION.equals(operation)) {
            //on 条件
            for (String primaryColumn : primaryColumns) {
                int pos = ArrayUtils.indexOfNoCase(fieldNames, primaryColumn);
                Object value = objects[pos];
                setValues(objects, index, value);
                index++;
            }
            //insert
            for (int i = 0; i < columns; i++) {
                Object value = objects[i];
                setValues(objects, index, value);
                index++;
            }
            // update
            String[] mergeUpdateArray = org.apache.commons.lang3.ArrayUtils.removeElements(fieldNames, primaryColumns);
            for (String s : mergeUpdateArray) {
                int pos = ArrayUtils.indexOfNoCase(fieldNames, s);
                Object value = objects[pos];
                setValues(objects, index, value);
                index++;
            }
        }

        return String.format(sql.replaceAll("\\?", "%s"), objects);
    }

    /**
     * 根据SQL 获取操作类型
     */
    private static String getOperation(String sql) {
        if (sql == null) {
            throw new SqlBuildException("获取SQL类型失败, SQL为null");
        }
        sql = sql.toUpperCase().trim();

        if (sql.startsWith(INSERT_OPERATION)) {
            return INSERT_OPERATION;
        } else if (sql.startsWith(UPDATE_OPERATION)) {
            return UPDATE_OPERATION;
        } else if (sql.startsWith(DELETE_OPERATION)) {
            return DELETE_OPERATION;
        } else if (sql.startsWith(MERGE_OPERATION)) {
            return MERGE_OPERATION;
        } else if (sql.startsWith(TRANSLATE_OPERATION)) {
            return TRANSLATE_OPERATION;
        } else {
            throw new SqlBuildException("SQL类型不支持");
        }
    }

    /**
     * 获取实际传递参数个数
     *
     * @param operation      SQL类型
     * @param columns        表字段个数
     * @param primaryColumns 表主键个数
     */
    private static int getParamSize(String operation, int columns, int primaryColumns) {
        switch (operation) {
            case INSERT_OPERATION:
                return columns;
            case UPDATE_OPERATION:
                return columns + primaryColumns;
            case DELETE_OPERATION:
                return primaryColumns;
            case MERGE_OPERATION:
                //on 条件 + insert + 除主键外update
                return columns + columns;
            default:
                return 0;
        }

    }

    /**
     * 预编译？赋值
     *
     * @param values ？所有值
     * @param index  当前问号索引
     * @param value  当前问号值
     */
    private static void setValues(Object[] values, int index, Object value) {
        if (value instanceof Timestamp) {
            values[index] = "TO_TIMESTAMP('" + value + "', 'YYYY-MM-DD HH24:MI:SS.FF6')";
        } else if (value instanceof String) {
            values[index] = "'" + value + "'";
        } else {
            values[index] = value;
        }


    }

    /**
     * ? 和参数的实际个数是否匹配
     *
     * @param sql      SQL 语句，可以带有 ? 的占位符
     * @param paramNum 参数的实际个数
     * @return true 表示为 ? 和参数的实际个数匹配
     */
    private static boolean match(String sql, int paramNum) {

        Matcher m = compile("(\\?)").matcher(sql);
        int count = 0;
        while (m.find()) {
            count++;
        }
        return count == paramNum;
    }
    //----------------------------- debug sql end -----------------------------
}
